package qldc.pm.tiensu.service.persistence;

import java.util.Date;
import java.util.List;

import qldc.pm.tiensu.model.TienSu;

import com.liferay.portal.kernel.dao.orm.QueryPos;
import com.liferay.portal.kernel.dao.orm.QueryUtil;
import com.liferay.portal.kernel.dao.orm.SQLQuery;
import com.liferay.portal.kernel.dao.orm.Session;
import com.liferay.portal.kernel.dao.orm.Type;
import com.liferay.portal.kernel.exception.SystemException;
import com.liferay.portal.kernel.log.Log;
import com.liferay.portal.kernel.log.LogFactoryUtil;
import com.liferay.portal.kernel.util.StringPool;
import com.liferay.portal.kernel.util.StringUtil;
import com.liferay.portal.kernel.util.Validator;
import com.liferay.portal.service.persistence.impl.BasePersistenceImpl;
import com.liferay.util.dao.orm.CustomSQLUtil;

public class TienSuFinderImpl extends BasePersistenceImpl<TienSu> implements
		TienSuFinder {
	private static Log _log = LogFactoryUtil.getLog(TienSuFinder.class);

	public static final String FIND_BY_TKTS_DIAPHUONG_PHUONGXA = TienSuFinder.class
			.getName() + ".findByTKTS_Diaphuong_PhuongXa";
	public static final String FIND_BY_TKTS_DIAPHUONG_QUANHUYEN = TienSuFinder.class
			.getName() + ".findByTKTS_Diaphuong_QuanHuyen";

	@SuppressWarnings("unchecked")
	public List<Object[]> findByTKTS_Diaphuong_PhuongXa(long tinhId, long quanHuyenId,
			Date tuNgay, Date denNgay) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_TKTS_DIAPHUONG_PHUONGXA);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
			}

			if (quanHuyenId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.quanHuyenId = ?)",
						StringPool.BLANK);

			}

			if (Validator.isNull(tuNgay)) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_QuanHeVoiChuHo.ngayChuyenDen > ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgay)) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_QuanHeVoiChuHo.ngayChuyenDen <= ?)",
						StringPool.BLANK);
			}


			SQLQuery q = session.createSQLQuery(sql);
			q.addScalar("diaGioiHanhChinh", Type.LONG);

			q.addScalar("HoNhanKhau", Type.LONG);
			q.addScalar("NhanKhau", Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}

			if (quanHuyenId > 0) {
				qpos.add(quanHuyenId);
			}

			if (Validator.isNotNull(tuNgay)) {
				qpos.add(tuNgay);
			}

			if (Validator.isNotNull(denNgay)) {
				qpos.add(denNgay);
			}

			return (List<Object[]>) q.list();
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
	
	@SuppressWarnings("unchecked")
	public List<Object[]> findByTKTS_Diaphuong_QuanHuyen(long tinhId,
			Date tuNgay, Date denNgay) throws SystemException {
		Session session = null;

		try {
			session = openSession();

			String sql = CustomSQLUtil.get(FIND_BY_TKTS_DIAPHUONG_QUANHUYEN);

			if (tinhId <= 0) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_NoiThuongTru.tinhId = ?)", StringPool.BLANK);
				return null;
			}


			if (Validator.isNull(tuNgay)) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_QuanHeVoiChuHo.ngayChuyenDen > ?)",
						StringPool.BLANK);
			}

			if (Validator.isNull(denNgay)) {
				sql = StringUtil.replace(sql,
						"AND (CSDL_QuanHeVoiChuHo.ngayChuyenDen <= ?)",
						StringPool.BLANK);
			}


			SQLQuery q = session.createSQLQuery(sql);
			q.addScalar("diaGioiHanhChinh", Type.LONG);

			q.addScalar("HoNhanKhau", Type.LONG);
			q.addScalar("NhanKhau", Type.LONG);

			QueryPos qpos = QueryPos.getInstance(q);

			if (tinhId > 0) {
				qpos.add(tinhId);
			}


			if (Validator.isNotNull(tuNgay)) {
				qpos.add(tuNgay);
			}

			if (Validator.isNotNull(denNgay)) {
				qpos.add(denNgay);
			}

			return (List<Object[]>) q.list();
		} catch (Exception e) {
			throw new SystemException(e);
		} finally {
			closeSession(session);
		}
	}
}
